﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using ModelLayer;

namespace DBLayer
{
    public class DBBooking
    {
        private SqlConnection con;

        public DBBooking()
        {
            con = DBConnection.getInstance().getDBCon();
        }

        public bool UpdateBooking(int bookingId, int pbId, bool isCollected)
        {
            bool updated = false;

            SqlParameter bookingIdParam = new SqlParameter("@bookingId", SqlDbType.Int);
            bookingIdParam.Value = bookingId;

            SqlParameter pbIdParam = new SqlParameter("@pbId", SqlDbType.Int);
            pbIdParam.Value = pbId;

            SqlParameter isCollectedParam = new SqlParameter("@IsCollected", SqlDbType.Bit );
            isCollectedParam.Value = isCollected;

            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandText = "UPDATE Booking SET partBk_id=@pbId, " + 
                              "IsCollected=@isCollected " +
                              "WHERE id = @bookingId";
          
            com.Parameters.Add(bookingIdParam);
            com.Parameters.Add(pbIdParam);
            com.Parameters.Add(isCollectedParam);


           
            try
            {
                if (com.ExecuteNonQuery() != 0)
                {
                    updated = true;
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine("Error: " + e);
            }

            return updated;
        }

        public int CreateBooking(int empId, float totalPrice)
        {
            int bookingID = 0;
            SqlDataReader reader = null;


            SqlParameter empIdParam = new SqlParameter("@empId", SqlDbType.Int, 10);
            empIdParam.Value = empId;

            SqlParameter totalPriceParam = new SqlParameter("@totalPrice", SqlDbType.Int);
            totalPriceParam.Value = totalPrice;



            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandText = "INSERT INTO Booking (employee_Id, totalPrice) " +
                              "VALUES (@empId, @totalPrice) " +
                              "SELECT CAST(SCOPE_IDENTITY() AS int)";
            com.Parameters.Add(totalPriceParam);
            com.Parameters.Add(empIdParam);
           
            try
            {
                reader = com.ExecuteReader();

                if (reader.Read())
                    bookingID = reader.GetInt32(0);

                reader.Close();
            }
            catch (SqlException e)
            {
                Console.WriteLine("Error: " + e);
            }

            return bookingID;
        }


        public Booking GetBooking(int bookingId)
        {
            Booking booking = null;
            SqlDataReader reader = null;
            

            SqlParameter idParam = new SqlParameter("@bookingId", SqlDbType.Int);
            idParam.Value = bookingId;

            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandText = "SELECT employee_Id, isCollected, totalPrice FROM Booking WHERE id = @bookingId";
            com.Parameters.Add(idParam);

            try
            {
                reader = com.ExecuteReader();

                while (reader.Read())
                {
                    booking = new Booking(bookingId, reader.GetInt32(0), reader.GetBoolean(1), (float)reader.GetDecimal(2));

                }

                reader.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
            }

            return booking;

        }

        public List<Booking> GetBookingsFromCustomer(Customer cust)
        {
            List<Booking> custBookings = new List<Booking>();

            SqlDataReader reader = null;

            SqlParameter custIdparam = new SqlParameter("@cust_id", SqlDbType.Int);
            custIdparam.Value = cust.CustomerId;

            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandText = "SELECT id, employee_Id, isCollected, totalPrice FROM Booking " +
                              "INNER JOIN Customer " +
                              "ON Booking.id = Customer.booking_Id " +
                              "WHERE cust_Id = @cust_id";
            com.Parameters.Add(custIdparam);

            try
            {
                reader = com.ExecuteReader();

                while (reader.Read())
                {
                    custBookings.Add(new Booking(reader.GetInt32(0), reader.GetInt32(1), reader.GetBoolean(1), (float)reader.GetDecimal(2)));
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine("Error: " + e);
            }

            return custBookings;
        }
    }
}
